# coding: utf-8

# NOTE by zhouhl 2018-01-26
# Step1. 针对真实号码
# 对mobile相同的user进行处理，只保留一个user记录
# 同步user信息，更新/删除其他表关联的uid
# 最后对 User.mobile 增加唯一索引

# Step2. 针对虚拟号码
# 全部删除相关记录

import os
import set_env_path     # 请勿删除

from sqlalchemy import create_engine
from sqlalchemy.sql import text


engine = create_engine(
    os.getenv('DATABASE_URL')
    or 'mysql+pymysql://root:@127.0.0.1/wph_www_prod?charset=utf8'
)
conn = engine.connect()


def find_duplicate_mobile():
    """
    查找关联了多个用户的电话
    """
    sql = text("""
        select mobile,count(*) as c_num from user group by mobile having c_num>1 order by c_num desc;
    """)

    records = conn.execute(sql).fetchall()
    duplicate_mobile_list = [i[0] for i in records]
    return duplicate_mobile_list


def judge_reserved_user(mobile):
    """
    判断该保留哪个账号
    """
    valid_users = []

    # 根据mobile查找user记录
    get_user_records = text("""
        select
            id, nickname, deleted_at, wx_openid, mobile,
            has_set_business, status, recent_login_at
        from
            user
        where
            mobile=:mobile
        order by id asc;
    """)

    source_users = conn.execute(get_user_records, mobile=mobile).fetchall()

    # 根据 deleted_at 选择 user
    for i in source_users:
        if i.deleted_at == 0:
            valid_users.append(i)

    if len(valid_users) == 0:
        # 此时所有账号均被删除
        print('return at deleted_at')
        return None, source_users

    if len(valid_users) == 1:
        sync_user_info(valid_users[0], source_users)
        print('return at deleted_at')
        return valid_users[0], source_users


    # 根据bill是否存在uid判断
    tmp_users = valid_users
    valid_users = []
    find_bill_records = text("""
        select count(*) from bill where uid=:uid;
    """)
    for i in tmp_users:
        tmp_result = conn.execute(find_bill_records, uid=i.id).fetchone()
        if tmp_result[0]:
            valid_users.append(i)

    if len(valid_users) == 1:
        sync_user_info(valid_users[0], source_users)
        print('return at bill')
        return valid_users[0], source_users
    else:
        valid_users = []


    # 根据是否抢过单判断，partic.supplier_id
    find_partic_records = text("""
        select count(*) from partic where supplier_id=:uid;
    """)
    for i in tmp_users:
        tmp_result = conn.execute(find_partic_records, uid=i.id).fetchone()
        if tmp_result[0]:
            valid_users.append(i)

    if len(valid_users) == 1:
        sync_user_info(valid_users[0], source_users)
        print('return at partic')
        return valid_users[0], source_users
    else:
        valid_users = []


    # 没有其他有效信息，只好根据最近登录的时间 recent_login_at 来判断了
    valid_user = tmp_users[0]
    for i in tmp_users[1:]:
        if i.recent_login_at > valid_user.recent_login_at:
            valid_user = i
    sync_user_info(valid_user, source_users)
    print('return at recent_login_at')
    return valid_user, source_users


def sync_user_info(valid_user, source_users):
    """
    更新最终保留user记录的信息
    """

    if not valid_user:
        return

    sync_user_info = text("""
        update
            user
        set
            nickname=:nickname, wx_openid=:wx_openid,
            has_set_business=:has_set_business, status=:status
        where
            id=:id;
    """)

    info = {
        'nickname':valid_user.nickname,
        'wx_openid':valid_user.wx_openid,
        'has_set_business':valid_user.has_set_business,
        'status':valid_user.status,
    }
    for i in source_users:
        if i.nickname != i.mobile:
            info['nickname'] = i.nickname
        if i.wx_openid != '':
            info['wx_openid'] = i.wx_openid
        if i.has_set_business:
            info['has_set_business'] = i.has_set_business
        if i.status:
            info['status'] = i.status

    conn.execute(
        sync_user_info,
        nickname=info['nickname'],
        wx_openid=info['wx_openid'],
        has_set_business=info['has_set_business'],
        status=info['status'],
        id=valid_user.id
    )


def update_foreign_key(valid_user_id, invalid_user_ids):
    """
    更新相关表的user_id
    """
    table_col_list = [
        ('audio', 'uid'),
        ('bill', 'uid'),
        ('demand', 'uid'),
        ('partic', 'supplier_id'),
        ('demand_finish_imgs', 'user_id'),
        ('im_messages', 'im_from'),
        ('im_messages', 'im_to'),
        ('demand_clients', 'user_id'),
        ('virtual_numbers', 'user_id'),
        ('outbound_records', 'user_id'),
        ('product_prices', 'supplier_id'),
        ('product_desc', 'supplier_id'),
        ('orders', 'seller_id'),
        ('orders', 'buyer_id'),
        ('order_remarks', 'user_id'),
        ('product_drafts', 'applicant_id'),
        ('cart_items', 'user_id'),
        ('cart_items', 'supplier_id'),
        ('personal_dynamic', 'user_id'),
        ('dynamic_reply', 'user_id'),
        ('dynamic_like', 'user_id'),
        ('supplier_demand', 'user_id')
    ]

    base_update_sql = """
        update
            {table_name}
        set
            {uid_col} = :valid_user_id
        where
            {uid_col} in :invalid_user_ids;
    """

    base_delete_sql = """
        delete from
            {table_name}
        where
            {uid_col} in :invalid_user_ids;
    """

    for item in table_col_list:
        if valid_user_id:
            update_sql = text(base_update_sql.format(
                table_name=item[0],
                uid_col=item[1],
            ))
            conn.execute(
                update_sql,
                valid_user_id=valid_user_id,
                invalid_user_ids=invalid_user_ids,
            )
        else:
            delete_sql = text(base_delete_sql.format(
                table_name=item[0],
                uid_col=item[1],
            ))
            conn.execute(
                delete_sql,
                invalid_user_ids=invalid_user_ids,
            )


def delete_foreign_key(valid_user_id, invalid_user_ids):
    """
    删除相关表的user_id记录
    """
    table_col_list = [
        ('pack', 'uid'),
        ('user_info', 'user_id'),
        ('user_tags', 'user_id'),
        ('user_protocol', 'user_id'),
        ('bid_cost_record', 'user_id'),
        ('mobile_lists', 'follower_id'),
        ('mobile_lists', 'followed_id'),
        ('invite_logs', 'follower_id'),
        ('invite_logs', 'followed_id'),
        ('wechat_send_log', 'user_id')
    ]

    base_delete_sql = """
        delete from
            {table_name}
        where
            {uid_col} in :invalid_user_ids;
    """

    for item in table_col_list:
        delete_sql = text(base_delete_sql.format(
            table_name=item[0],
            uid_col=item[1],
        ))
        conn.execute(
            delete_sql,
            invalid_user_ids=invalid_user_ids,
        )


def delete_invalid_users(invalid_user_ids):
    """
    删除无效的user记录
    """
    delete_sql = text("""
        delete from user where id in :invalid_user_ids
    """)
    conn.execute(
        delete_sql,
        invalid_user_ids=invalid_user_ids,
    )


if __name__ == '__main__':
    update_exists_price_sql = find_duplicate_mobile()
    for mobile in update_exists_price_sql:
        valid_user, source_users = judge_reserved_user(mobile)
        print(mobile, valid_user, '\n')

        # 保留账户的id与删除账户的id
        valid_user_id = valid_user.id if valid_user else 0
        invalid_user_ids = [i.id for i in source_users if i.id != valid_user_id]
        update_foreign_key(valid_user_id, invalid_user_ids)
        delete_foreign_key(valid_user_id, invalid_user_ids)
        delete_invalid_users(invalid_user_ids)


